﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class ChucNang_NguoiDung_NguoiDung_TimKiemSanPham : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            DataDSTableAdapters.PHANLOAITableAdapter pladapter = new DataDSTableAdapters.PHANLOAITableAdapter();
            DataDS.PHANLOAIDataTable pltable = pladapter.PHANLOAI_LayPhanLoaiTheoIDCha(0);
            /*if (pltable.Rows.Count == 0)
                return;
            foreach (DataDS.PHANLOAIRow row in pltable)
            {*/
                TreeNode treenode = new TreeNode();
                treenode.Text = "Phân loại";
                treenode.Value = "0";
                treenode.PopulateOnDemand = true;
                treenode.SelectAction = TreeNodeSelectAction.SelectExpand;
                tvPhanLoai.Nodes.Add(treenode);

            //}
               // txtTenSP.Text = ConfigurationManager.ConnectionStrings["SMOConnectionString"].ConnectionString;
        }
    }
    protected void tvPhanLoai_TreeNodePopulate(object sender, TreeNodeEventArgs e)
    {
        string idcha = e.Node.Value;
        DataDSTableAdapters.PHANLOAITableAdapter pladapter = new DataDSTableAdapters.PHANLOAITableAdapter();
        DataDS.PHANLOAIDataTable pltable = pladapter.PHANLOAI_LayPhanLoaiTheoIDCha(int.Parse(idcha));
        if (pltable.Rows.Count == 0)
            return;
        foreach (DataDS.PHANLOAIRow row in pltable)
        {
            TreeNode treenode = new TreeNode();
            treenode.Text = row["TenPhanLoai"].ToString();
            treenode.Value = row["Id"].ToString();
            treenode.PopulateOnDemand = true;
            treenode.SelectAction = TreeNodeSelectAction.SelectExpand;
            e.Node.ChildNodes.Add(treenode);

        }
    }
    public string LayCon(string idcha)
    {
        string kq = "";
        DataDSTableAdapters.PHANLOAITableAdapter pladapter = new DataDSTableAdapters.PHANLOAITableAdapter();
        DataDS.PHANLOAIDataTable pltable = pladapter.PHANLOAI_LayPhanLoaiTheoIDCha(int.Parse(idcha));
        if (pltable.Rows.Count == 0)
            return ("_"+idcha);
        foreach (DataDS.PHANLOAIRow row in pltable)
        {
            string id = row["Id"].ToString();
            kq+=LayCon(id);
        }
        return kq;
    }
    protected void btnTim_Click(object sender, EventArgs e)
    {
        if (Session["TimKiem"] != null)
            Session.Remove("TimKiem");
        if (Session["TongSo"] != null)
            Session.Remove("TongSo");
        if (Session["HienThi"] != null)
            Session.Remove("HienThi");
        string value = tvPhanLoai.SelectedValue;
        if (value == "")
            value = "0";
        string kq = LayCon(value);
        string[] a=kq.Split('_');
      //  Response.Write(kq);
        string query = "";
        if (a.Length - 1 > 1)
        {

            query = "select MaSP,TenSP,HinhAnh,GiaCu,ROW_NUMBER() OVER(ORDER BY TenSP ASC) AS RowRank from SANPHAM where 1=1";
            if (txtTenSP.Text != "")
                query += " and TenSP like N'%"+txtTenSP.Text+"%'";
            query+= " and (0=1";
            for (int i = 1; i < a.Length; i++)
            {
                query += " or Id_PhanLoai=" + a[i];
            }
            query += " )";
        }
        else
        {
            string strconnection = ConfigurationManager.ConnectionStrings["SMOConnectionString"].ConnectionString;
            System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(strconnection);
            connection.Open();
            query = "select tt.* " +
                           "from PHANLOAI_THUOCTINH pltt, THUOCTINH tt " +
                           "where pltt.Id_ThuocTinh=tt.Id and pltt.Id_PhanLoai=" + value;
            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
            command.CommandText = query;
            command.Connection = connection;
            System.Data.SqlClient.SqlDataReader dr = command.ExecuteReader();
            if (dr.HasRows)
            {
                query = "select distinct sp.MaSP,sp.TenSP,sp.HinhAnh,sp.GiaCu,ROW_NUMBER() OVER(ORDER BY TenSP ASC) AS RowRank " +
                        "from SANPHAM sp, CHITIET_THUOCTINH cttt " +
                         "where sp.Id=cttt.Id_SP and sp.Id_PhanLoai=" + value;
                if (txtTenSP.Text != "")
                    query += " and sp.TenSP like N'%" + txtTenSP.Text + "%'";
                query += " and (0=1";
                int dem= 0;
                while (dr.Read())
                {
                    string id = dr["Id"].ToString();
                    string ten = dr["TenThuocTinh"].ToString();
                    if (Request.Form.Get(id) != "")
                    {
                        query += " or( cttt.Id_ThuocTinh=" + id + " and cttt.GiaTri like N'%" + Request.Form.Get(id) + "%' )";
                        dem++;
                    }
                }
                query += " ) ";
                if (dem == 0)
                    query = query.Replace("0=1", "1=1");
                else
                {
                    //and hay or
                    query += " group by sp.MaSP,sp.TenSP,sp.HinhAnh,sp.GiaCu,sp.ThoiGianDang having count(*)="+dem.ToString();
                }
            }
            else
            {
                query = "select MaSP,TenSP,HinhAnh,GiaCu,ROW_NUMBER() OVER(ORDER BY TenSP ASC) AS RowRank " +
                        "from SANPHAM  " +
                        "where Id_PhanLoai=" + value;
                if (txtTenSP.Text != "")
                    query += " and TenSP like N'%" + txtTenSP.Text + "%'";

            }

        }
     /*  query = "select Id,TenSP,HinhAnh,GiaCu "+
                "from (" + query +")as BANG_WITHNUM where RowRank >@Start and RowRank<=(@Start + @Num)";
        Session["timkiem"] = query;
        Response.Redirect("NguoiDung_KetQuaTimKiem.aspx");*/
        string query1 = query.Replace("MaSP,TenSP,HinhAnh,GiaCu,ROW_NUMBER() OVER(ORDER BY TenSP ASC) AS RowRank", "count (distinct MaSP) as NUMBER");
        query1 = query1.Replace("distinct sp.MaSP,sp.TenSP,sp.HinhAnh,sp.GiaCu,ROW_NUMBER() OVER(ORDER BY TenSP ASC) AS RowRank", "count (distinct sp.MaSP) as NUMBER");
        Response.Write(query1);
        string sconnection = ConfigurationManager.ConnectionStrings["SMOConnectionString"].ConnectionString;
        System.Data.SqlClient.SqlConnection oconnection = new System.Data.SqlClient.SqlConnection(sconnection);
        oconnection.Open();
        System.Data.SqlClient.SqlCommand ocommand = new System.Data.SqlClient.SqlCommand();
        ocommand.CommandText = query1;
        ocommand.Connection = oconnection;
        System.Data.SqlClient.SqlDataReader dr1= ocommand.ExecuteReader();
        int tong=0;
        while (dr1.Read())
        {
            tong = (int)dr1["NUMBER"];
        }
        oconnection.Close();
        Response.Write(tong.ToString());
       query = "select MaSP,TenSP,HinhAnh,GiaCu " +
                "from (" + query + ")as BANG_WITHNUM where RowRank >@Start and RowRank<=(@Start + @Num)";
        Session["TimKiem"] = query;
        Session["TongSo"]=tong;
        Session["HienThi"] = 1;
        Response.Redirect("NguoiDung_KetQuaTimKiem.aspx");
    }
}
